package com.bdmc.smokesensor.services;

import java.io.InputStream;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import com.bdmc.smokesensor.Utils.MyException;
import com.bdmc.smokesensor.dao.Smk_DevicesJPA;
import com.bdmc.smokesensor.entity.Smk_Devices;
import com.bdmc.smokesensor.iservices.ISmk_DevicesService;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Example;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;

@Service
public class Smk_DevicesService extends BaseService<Smk_Devices> implements ISmk_DevicesService {

    @Autowired
    public Smk_DevicesJPA _devJPA;

    @Override
    public List<Smk_Devices> findAll() {

        String sql = "select * from smk_devices";
        List<Smk_Devices> list = GetListBySql(sql, Smk_Devices.class);
        return list;
    }

    @Override
    public Smk_Devices findByImei(String imei) {
        return _devJPA.findByImei(imei);
    }

    @Override
    public Smk_Devices findByDevId(String devId) {
        return _devJPA.findByDevId(devId);
    }

    public Smk_Devices findByImeiAndIdNot(String imei, int id) {
        return _devJPA.findByImeiAndIdNot(imei, id);
    }

    @Override
    public Map<String, Object> findByPageable(int depid, String devName, int gc_id, int page, int size) {
        String sql = "select dv.*,bd.name as bdName,bd.addr as bdAddr,bd.managerName,bd.managerMobile,gc.groupName,dep.depName "
                + " from smk_devices dv left join sys_department dep on dv.departmentId=dep.id "
                + " left join smk_buildings bd on bd.id = dv.smkBuildingId "
                + " left join smk_groupcompany gc on gc.id = bd.smkGroupCompanyId" + " where dep.id=" + depid
                + " or dep.treeRoot like '%," + depid + ",%' ";

        if (devName != null && devName.length() > 0)
            sql += " and dv.devName like '%" + devName + "%' ";
        if (gc_id > 0)
            sql += " and gc.id= " + gc_id;
        List<Map<String, Object>> data = getListMapBySql(sql, page, size);
        int total = GetCountBySql(sql);

        Map<String, Object> map = new HashMap<String, Object>();
        map.put("total", total);
        map.put("data", data);
        return map;
    }

    @Override
    public List<Map<String, Object>> findForExcel(int depid, String devName, int gc_id) {
        String sql = "select dv.devName,dv.devModel,dv.imei,dv.iccid,dv.operator,bd.name as bdName,bd.addr as bdAddr,dv.installDate,dv.installAddr,"
        + "bd.managerName,bd.managerMobile,gc.groupName,dep.depName "
                + " from smk_devices dv left join sys_department dep on dv.departmentId=dep.id "
                + " left join smk_buildings bd on bd.id = dv.smkBuildingId "
                + " left join smk_groupcompany gc on gc.id = bd.smkGroupCompanyId" + " where dep.id=" + depid
                + " or dep.treeRoot like '%," + depid + ",%' ";

        if (devName != null && devName.length() > 0)
            sql += " and dv.devName like '%" + devName + "%' ";
        if (gc_id > 0)
            sql += " and gc.id= " + gc_id;
        List<Map<String, Object>> data = getListMapBySql(sql);
        return data;
    }

    @Override
    public Map<String, Object> findDevTypeSum(int depid) {
        String sql = "select max(case WHEN dev.devStatus=1 then 1 else 0 end ) as 'onlineDev' ,"
        + " sum(case WHEN dev.devStatus=2 then 1 else 0 end ) as 'offlineDev', "
        + " sum(case WHEN dev.smkBuildingId>0 then 1 else 0 end ) as 'bindDev',"
        + " sum(case WHEN dev.smkBuildingId=0 then 1 else 0 end ) as 'unbindDev'"
        + " from smk_devices dev"
        + " left JOIN sys_department dep on dev.departmentId=dep.id"
        + " where dep.id="+depid+" or dep.treeRoot  like '%," + depid + ",%' ";
        List<Map<String, Object>> data = getListMapBySql(sql);
        return data.get(0);
    }

    @Override
    public Smk_Devices findOne(Example<Smk_Devices> example) {
        return _devJPA.findOne(example).orElse(null);
    }

    @Override
    public Smk_Devices findOneById(int id) {
        return _devJPA.findById(id).orElse(null);
    }

    @Override
    public Smk_Devices save(Smk_Devices d) {
        if (d.getId() < 1)
            d.setJoinDate(new Date());
        else
            d.setUpdateDate(new Date());
        return _devJPA.save(d);
    }

    @Override
    public void deleteById(int id) {
        _devJPA.deleteById(id);
    }

    @Override
    public void updateDelete(int id) {
        Smk_Devices d = _devJPA.findById(id).orElse(null);
        d.setDel(true);
        _devJPA.save(d);
    }

    @Override
    @Transactional(readOnly = false, rollbackFor = Exception.class)
    public Map<String, Integer> batchImport(String fileName, MultipartFile file) throws Exception {
        Map<String, Integer> map = new HashMap<String, Integer>();
        List<Smk_Devices> devList = new ArrayList<Smk_Devices>();
        if (!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$")) {
            throw new MyException("上传文件格式不正确");
        }
        boolean isExcel2003 = true;
        if (fileName.matches("^.+\\.(?i)(xlsx)$")) {
            isExcel2003 = false;
        }
        InputStream is = file.getInputStream();
        Workbook wb = null;
        if (isExcel2003) {
            wb = new HSSFWorkbook(is);
        } else {
            wb = new XSSFWorkbook(is);
        }
        Sheet sheet = wb.getSheetAt(0);
        for (int r = 1; r <= sheet.getLastRowNum(); r++) {
            Row row = sheet.getRow(r);
            if (row == null) {
                continue;
            }

            String devName = row.getCell(0).getStringCellValue();
            String devModel = row.getCell(1).getStringCellValue();
            String imei = row.getCell(2).getStringCellValue();

            if (devName == null || devName.isEmpty()) {
                throw new MyException("导入失败(第" + (r + 1) + "行,设备名称未填写)");
            }
            if (devModel == null || devModel.isEmpty()) {
                throw new MyException("导入失败(第" + (r + 1) + "行,设备型号未填写)");
            }
            if (imei == null || imei.isEmpty()) {
                throw new MyException("导入失败(第" + (r + 1) + "行,imei未填写)");
            }
            Smk_Devices dev = new Smk_Devices();
            dev.setDevName(devName);
            dev.setDevModel(devModel);
            dev.setImei(imei);
            Example<Smk_Devices> example = Example.of(dev);
            Boolean isExist = _devJPA.exists(example);
            if (!isExist) {
                dev.setJoinDate(new Date());
                devList.add(dev);
            }
        }
        int succ = _devJPA.saveAll(devList).size();
        map.put("success", succ);
        map.put("error", sheet.getLastRowNum() - succ);
        return map;
    }

    @Override
    public List<Smk_Devices> findBySmkBuildingId(int smkBuildingId) {
        return _devJPA.findBySmkBuildingId(smkBuildingId);
    }

}